﻿create PROCEDURE Sync.SP_Receipts_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Receipts_Temp TABLE 
		(
			ReceiptID UniqueIdentifier,
			[ReceiptNumber] bigint,
				[ReceiptDate] datetime,
				[ReceiptTypeID] uniqueidentifier,
				[ReceiptAmount] float,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Receipts_Temp 
	   SELECT	Tbl.Col.value('ReceiptID[1]','uniqueidentifier') ReceiptID,
                Tbl.Col.value('ReceiptNumber[1]', 'bigint') [ReceiptNumber],
Tbl.Col.value('ReceiptDate[1]', 'datetime') [ReceiptDate],
Tbl.Col.value('ReceiptTypeID[1]', 'uniqueidentifier') [ReceiptTypeID],
Tbl.Col.value('ReceiptAmount[1]', 'float') [ReceiptAmount],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Receipts') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Receipts] ([ReceiptID],[ReceiptNumber],[ReceiptDate],[ReceiptTypeID],[ReceiptAmount])
	SELECT t.[ReceiptID],t.[ReceiptNumber],t.[ReceiptDate],t.[ReceiptTypeID],t.[ReceiptAmount]
	FROM  @Receipts_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Receipts_Tracking s
        WHERE t.ReceiptID = s.ReceiptID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Receipts]
	SET [Receipts].[ReceiptNumber] = t.[ReceiptNumber],[Receipts].[ReceiptDate] = t.[ReceiptDate],[Receipts].[ReceiptTypeID] = t.[ReceiptTypeID],[Receipts].[ReceiptAmount] = t.[ReceiptAmount] 
	FROM [dbo].[Receipts] s JOIN @Receipts_Temp t 
    ON t.ReceiptID = s.ReceiptID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Receipts_Tracking r
				  WHERE t.ReceiptID = r.ReceiptID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Receipts]
    FROM @Receipts_Temp t JOIN [dbo].[Receipts] s
    ON t.ReceiptID = s.ReceiptID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END














